/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.sql.apachederbylib.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import org.sql.apachederbylib.exception.SQLSintaxError;

/**
 *
 * @author Víctor Escobar
 */
public class SQL {
    
    /**
     * Execute a SQL query,be a DDL or DML query.
     * @param SQL The SQL sintax query,compatible with Apache Derby SQL99 or SQL92.
     * @param conn The connection resource given by conect method.
     * @return a ResultSet variable if the query is a select query,null in otherwise.
     * @throws SQLException This happen when you have an unknown error.Watch the message of the exception to find it out the cause.
     * @throws SQLSintaxError This happen when you have a sintax error in your SQL query.
     */
    public static ResultSet executeQuery(String sql,Connection conn) throws SQLException, SQLSintaxError{
        Statement stmt=conn.createStatement();
        try{
            if(sql.startsWith("select")){
                return stmt.executeQuery(sql);
            }
            else{
                stmt.execute(sql);
                return null;
            }
        }catch(SQLException ex){
                if(ex.getMessage().contains("sintax")){
                    throw new SQLSintaxError("You have a sintax error.Check your SQL query.");
                }
                else{
                    throw ex;
                }
        }
    }
    
    /**
     * This method transform the results of the SQL Query into a more confortable data structure for work with it.
     * @param rs The ResultSet variable given by executeQuery method.
     * @return This method return an array of rows.Each row stores the column results of the query.The columns order is the same that it appears in the SQL query.
     * @throws SQLException This happen when an error ocurred extracting the results of one row.
     */
    public static ArrayList<ArrayList<String>> parseSQLResultArray(ResultSet rs) throws SQLException{
        ArrayList<ArrayList<String>> array=new ArrayList();
        while(rs.next()){
            ArrayList<String> row=new ArrayList();
            for(int i=0;i<rs.getMetaData().getColumnCount();i++){
                row.add(rs.getString(i+1));
            }
            array.add(row);
        }
        return array;
    }
    
    /**
     * This method transform the results of the SQL Query into a more confortable data structure for work with it.
     * @param rs The ResultSet variable given by executeQuery method.
     * @return This method return an array of rows.Each row stores the column results of the query.The key of the Map is the name of the column.
     * @throws SQLException This happen when an error ocurred extracting the results of one row.
     */
    public static ArrayList<Map<String,String>> parseSQLResultMap(ResultSet rs) throws SQLException{
        ArrayList<Map<String,String>> array=new ArrayList();
        ArrayList<String> names=getColumnName(rs);
        while(rs.next()){
            Iterator it=names.iterator();
            Map<String,String> map=new HashMap();
            while(it.hasNext()){
                String columnName=String.valueOf(it.next());
                map.put(columnName, rs.getString(columnName));
            }
            array.add(map);
        }
        return array;
    }
    
    /**
     * This method give all the columns names given by a ResultSet variable.
     * @param rs The ResultSet variable given by executeQuery method.
     * @return An ArrayList<String>,the order is the same which appear in the SQL query.
     * @throws SQLException 
     */
    private static ArrayList<String> getColumnName(ResultSet rs) throws SQLException{
        ArrayList<String> names=new ArrayList();
        ResultSetMetaData metadatos=rs.getMetaData();
        for(int i=0;i<metadatos.getColumnCount();i++){
            names.add(metadatos.getColumnName(i+1));
        }
        return names;
    }
}
